<?php

namespace app\admin\controller;

use app\admin\controller\CommonController;
use think\Db;
use app\admin\model\Order;
use app\admin\model\Yuyue;
use app\admin\model\Store;
use app\admin\model\Staff;
use app\admin\model\Custom;
use app\admin\model\Item;
use app\admin\model\StoreVisit;
use app\admin\model\CtmSpecial;
use app\admin\model\ProgramRefund;
use app\admin\model\StoreTixian;
use app\admin\model\StaffTixian;
use app\admin\model\StoreCtm;
use app\admin\model\StoreStaff;
use app\admin\model\StaffBindStore;
use app\admin\model\StaffCtm;
use app\admin\model\StaffFlow;
use app\admin\model\ProvCityArea;
use app\util\Utils;
use app\admin\model\StaffFlowNew;
use app\admin\model\CtmCollect;
use app\admin\model\CtmCun;
use app\admin\model\CtmFlow;

class AgencyController extends CommonController {
    /*
     * 代理首页
     * author xfwed 
     * date 2020.08.18
     */

    public function agencyIndex() {
        $agency_id = input('param.agency_id'); //代理id
        $dateType = input('param.dateType'); //1昨日 2上周 3上月
        //今天
        $today = date("Y-m-d");
        //昨天
        $yesterday_start = date("Y-m-d 00:00:00", strtotime("-1 day"));
        $yesterday_end = date("Y-m-d 23:59:59", strtotime("-1 day"));
        //上周
        $lastweek_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7, date("Y")));
        $lastweek_end = date("Y-m-d H:i:s", mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7, date("Y")));
        //上月
        $beginTime1 = date('Y-m-01 00:00:00', strtotime('-1 month')); //上月开始时间
        $endTime1 = date("Y-m-d 23:59:59", strtotime(-date('d') . 'day')); //上月结束时间

        if ($dateType == 1) {
            $start = $yesterday_start;
            $end = $yesterday_end;
        } elseif ($dateType == 2) {
            $start = $lastweek_start;
            $end = $lastweek_end;
        } elseif ($dateType == 3) {
            $start = $beginTime1;
            $end = $endTime1;
        }
        $where['agency_id'] = $agency_id;
        //新增门店
        $newStoreNum = Store::where($where)->where('add_time', 'between', [$start, $end])->count();
        $data['newStoreNum'] = $newStoreNum;
        //总门店数
        $allStoreNum = Store::where($where)->count();
        $data['allStoreNum'] = $allStoreNum;
        //新增老板
        $newBossNum = Staff::alias('s')
                ->join('store_staff ss', 'ss.staff_id = s.id', 'left')
                ->join('store t', 't.id = ss.store_id', 'left')
                ->where('s.add_time', 'between', [$start, $end])
                ->where('t.agency_id', $agency_id)
                ->count();
        $data['newBossNum'] = $newBossNum;
        //总老板
        $allBossNum = Staff::alias('s')
                ->join('store_staff ss', 'ss.staff_id = s.id', 'left')
                ->join('store t', 't.id = ss.store_id', 'left')
                ->where('t.agency_id', $agency_id)
                ->count();
        $data['allBossNum'] = $allBossNum;
        //新增美容师数
        $newStaffNum = Staff::alias('s')
                ->join('staff_bind_store b', 'b.staff_id = s.id', 'left')
                ->join('store t', 't.id = b.store_id', 'left')
                ->where('b.add_time', 'between', [$start, $end])
                ->where('t.agency_id', $agency_id)
                ->count();
        $data['newStaffNum'] = $newStaffNum;
        //总美容师
        $allStaffNum = Staff::alias('s')
                ->join('staff_bind_store b', 'b.staff_id = s.id', 'left')
                ->join('store t', 't.id = b.store_id', 'left')
                ->where('t.agency_id', $agency_id)
                ->count();
        $data['allStaffNum'] = $allStaffNum;
        //新增顾客
        $store_ids = Store::where('agency_id', $agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $sql = "SELECT id FROM (
                    SELECT MIN(id) id,MIN(add_time) add_time,ctm_id,store_id
                    FROM ai_store_ctm
                    WHERE store_id in (" . $store_ids . ") GROUP BY ctm_id
                ) AS a
                WHERE 1=1
                ";
        $res = Db::query($sql);
        foreach ($res as $k => $v) {
            $ids6[] = $v['id'];
        }
        //print_r($ids6);
        $newCtmNum = StoreCtm::alias('s')
                ->where('s.add_time', 'between', [$start, $end])
                ->where('s.id', 'in', $ids6)
                ->order('s.add_time asc')
                ->count();
        //print_r(StoreCtm::getLastSql());
        $data['newCtmNum'] = $newCtmNum;
        //总顾客
        $allCtmNum = StoreCtm::where('id', 'in', $ids6)
                ->where('store_id', 'in', $store_ids)
                ->order('add_time asc')
                ->count();
        $data['allCtmNum'] = count($ids6);
        //新增消费单数
        $newOrderNum = Order::where('status', 2)
                ->where('store_id', 'in', $store_ids)
                ->where('add_time', 'between', [$start, $end])
                ->count();
        $newGoodsOrderNum = Db::table('pay_order')
                ->where('status', '>', 1)
                ->where('store_id', 'in', $store_ids)
                ->where('create_time', 'between', [$start, $end])
                ->count();
        $data['newOrderNum'] = $newOrderNum;
        $data['newGoodsOrderNum'] = $newGoodsOrderNum; //新增商品消费单数
        //总消费单数
        $allOrderNum = Order::where('status', 2)
                ->where('store_id', 'in', $store_ids)
                ->count();
        $allGoodsOrderNum = Db::table('pay_order')
                ->where('status', '>', 1)
                ->where('store_id', 'in', $store_ids)
                ->count();
        $data['allOrderNum'] = $allOrderNum;
        $data['allGoodsOrderNum'] = $allGoodsOrderNum;
        //新增收入
        $newOrderMoney = Order::where('status', 2)
                ->where('store_id', 'in', $store_ids)
                ->where('add_time', 'between', [$start, $end])
                ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                ->find();
        $newGoodsOrderMoney = Db::table('pay_order')
                ->where('status', '>', 1)
                ->where('store_id', 'in', $store_ids)
                ->where('create_time', 'between', [$start, $end])
                ->sum('pay_money');
        $data['newOrderMoney'] = $newOrderMoney['pay_money'] + $newOrderMoney['balance_money'];
        $data['newGoodsOrderMoney'] = $newGoodsOrderMoney;
        //总收入
        $allOrderMoney = Order::where('status', 2)
                ->where('store_id', 'in', $store_ids)
                ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                ->find();
        $allGoodsOrderMoney = Db::table('pay_order')
                ->where('status', '>', 1)
                ->where('store_id', 'in', $store_ids)
                ->sum('pay_money');
        $data['allOrderMoney'] = $allOrderMoney['pay_money'] + $allOrderMoney['balance_money'];
        $data['allGoodsOrderMoney'] = $allGoodsOrderMoney;
        //新增申请退款金额
        $newRefundNum = Yuyue::alias('y')
                ->join('program_refund p', 'p.yuyue_id = y.id', 'left')
                ->where('y.refund_status', 1)
                ->where('y.store_id', 'in', $store_ids)
                ->where('p.status', 1)
                ->where('p.add_time', 'between', [$start, $end])
                ->sum('money');
        $newGoodsRefundNum = Db::table('pay_order_goods')->alias('p')
                ->join('sell_goods_refund s', 's.pay_orders_goods_id = p.id', 'left')
                ->join(['pay_order_detail' => 'd'], 'd.id = p.pay_order_id', 'left')
                ->where('p.is_refund', 1)
                ->where('s.status', 1)
                ->where('d.store_id', 'in', $store_ids)
                ->where('s.add_time', 'between', [$start, $end])
                ->order('s.id desc')
                ->field('sum(price*number) num')
                ->find();
        $data['newRefundNum'] = $newRefundNum;
        $data['newGoodsRefundNum'] = $newGoodsRefundNum['num'];
        //总申请退款金额
        $allRefundNum = Yuyue::alias('y')
                ->join('program_refund p', 'p.yuyue_id = y.id', 'left')
                ->where('y.refund_status', 1)
                ->where('y.store_id', 'in', $store_ids)
                ->where('p.status', 1)
                ->sum('money');
        $allGoodsRefundNum = Db::table('pay_order_goods')->alias('p')
                ->join('sell_goods_refund s', 's.pay_orders_goods_id = p.id', 'left')
                ->join(['pay_order_detail' => 'd'], 'd.id = p.pay_order_id', 'left')
                ->where('p.is_refund', 1)
                ->where('s.status', 1)
                ->where('d.store_id', 'in', $store_ids)
                ->order('s.id desc')
                ->field('sum(price*number) num')
                ->find();
        $data['allRefundNum'] = $allRefundNum;
        $data['allGoodsRefundNum'] = $allGoodsRefundNum['num'];
        //总退款成功金额
        $allRefundSuccessNum = Yuyue::alias('y')
                ->join('program_refund p', 'p.yuyue_id = y.id', 'left')
                ->where('y.refund_status', 2)
                ->where('y.store_id', 'in', $store_ids)
                ->where('p.status', 1)
                ->sum('money');
        $allGoodsRefundSuccessNum = Db::table('pay_order_goods')->alias('p')
                ->join('sell_goods_refund s', 's.pay_orders_goods_id = p.id', 'left')
                ->join(['pay_order_detail' => 'd'], 'd.id = p.pay_order_id', 'left')
                ->where('p.is_refund', 2)
                ->where('s.status', 1)
                ->where('d.store_id', 'in', $store_ids)
                ->order('s.id desc')
                ->field('sum(price*number) num')
                ->find();
        $data['allRefundSuccessNum'] = $allRefundSuccessNum;
        $data['allGoodsRefundSuccessNum'] = $allGoodsRefundSuccessNum['num'];

        //美容师接单量排行
        $yuyueData = Yuyue::alias('y')
                ->join('staff s', 's.id = y.staff_id', 'left')
                ->where('y.status', 'in', '4,7,8')
                ->where('y.store_id', 'in', $store_ids)
                ->group('y.staff_id')
                ->field("count(y.id) value,s.staff_name name")
                ->order("value desc")
                ->limit(0, 6)
                ->select();

        $data['staffData'] = $yuyueData;

        //客户类型占比
        $data['commonCtmNum'] = StoreCtm::alias('s')
                ->join('custom c', 'c.id = s.ctm_id', 'left')
                ->where('s.store_id', 'in', $store_ids)
                ->where('c.vip_status', 1)//0新客户 1普通用户 2特权客户 
                ->group('s.ctm_id')
                ->count();
        $data['newCtmsNum'] = StoreCtm::alias('s')
                ->join('custom c', 'c.id = s.ctm_id', 'left')
                ->where('s.store_id', 'in', $store_ids)
                ->where('c.vip_status', 0)//0新客户 1普通用户 2特权客户 
                ->group('s.ctm_id')
                ->count();
        $data['specialCtmNum'] = StoreCtm::alias('s')
                ->join('custom c', 'c.id = s.ctm_id', 'left')
                ->where('s.store_id', 'in', $store_ids)
                ->where('c.vip_status', 2)//0新客户 1普通用户 2特权客户 
                ->group('s.ctm_id')
                ->count();
        //店铺接单情况
        $yuyueData = Yuyue::alias('y')
                ->join('store s', 's.id = y.store_id', 'left')
                ->join('custom c', 'c.id = y.ctm_id', 'left')
                ->join('staff st', 'st.id = y.staff_id', 'left')
                ->where('y.status', 'in', '2,3,4,7,8')
                ->field('y.id,y.status,y.start_time,c.custom_name,s.store_name,st.nickname')
                ->order('y.start_time desc')
                ->limit(0, 20)
                ->select();
        $data['yuyueData'] = $yuyueData;

        return $this->ajaxReturn(0, '查询成功', $data);
    }

    /*
     * 老板管理
     * author xfwed 
     * date 2020.08.20
     */

    public function bossIndex() {
        $agency_id = input('param.agency_id'); //代理id
        $dateType = input('param.dateType'); //1昨日 2上周 3上月
        //
        $perPage = input('param.perPage');
        $page = input('param.page');
        //今天
        $today = date("Y-m-d");
        //昨天
        $yesterday_start = date("Y-m-d 00:00:00", strtotime("-1 day"));
        $yesterday_end = date("Y-m-d 23:59:59", strtotime("-1 day"));
        //上周
        $lastweek_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7, date("Y")));
        $lastweek_end = date("Y-m-d H:i:s", mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7, date("Y")));
        //上月
        $beginTime1 = date('Y-m-01 00:00:00', strtotime('-1 month')); //上月开始时间
        $endTime1 = date("Y-m-d 23:59:59", strtotime(-date('d') . 'day')); //上月结束时间

        if ($dateType == 1) {
            $start = $yesterday_start;
            $end = $yesterday_end;
        } elseif ($dateType == 2) {
            $start = $lastweek_start;
            $end = $lastweek_end;
        } elseif ($dateType == 3) {
            $start = $beginTime1;
            $end = $endTime1;
        }
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        //总老板
        $allBossList = Staff::alias('s')
                ->join('store_staff ss', 'ss.staff_id = s.id', 'left')
                ->join('store t', 't.id = ss.store_id', 'left')
                ->where('t.agency_id', $agency_id)
                ->field('s.id,s.staff_name,s.telephone')
                ->group('ss.staff_id')
                ->paginate($perPage);
        foreach ($allBossList as $k => $v) {
            //开店数量
            $store_ids = StoreStaff::where('staff_id', $v['id'])->column('store_id');
            $allBossList[$k]['storeNum'] = count($store_ids);
            //新增顾客及来源（占比）
            $store_ids = implode(',', $store_ids);
            $sql = "SELECT id FROM (
                    SELECT MIN(id) id,MIN(add_time) add_time,ctm_id,store_id
                    FROM ai_store_ctm
                    WHERE store_id in (" . $store_ids . ") GROUP BY ctm_id
                ) AS a
                WHERE 1=1
                ";
            $res = Db::query($sql);
            foreach ($res as $kk => $vv) {
                $ids6[] = $vv['id'];
            }

            $newCtmNum = StoreCtm::alias('s')
                    ->where('s.add_time', 'between', [$start, $end])
                    ->where('s.id', 'in', $ids6)
                    ->order('s.add_time asc')
                    ->count();
            $allBossList[$k]['newCtmNum'] = $newCtmNum;
            //总顾客及来源（占比）
            $allCtmNum = StoreCtm::where('id', 'in', $ids6)
                    ->where('store_id', 'in', $store_ids)
                    ->order('add_time asc')
                    ->count();
            $allBossList[$k]['allCtmNum'] = count($ids6);
            //新增美容师数
            $newStaffNum = Staff::alias('s')
                    ->join('staff_bind_store b', 'b.staff_id = s.id', 'left')
                    ->join('store t', 't.id = b.store_id', 'left')
                    ->where('b.add_time', 'between', [$start, $end])
                    ->where('t.id', 'in', $store_ids)
                    ->count();
            $allBossList[$k]['newStaffNum'] = $newStaffNum;
            //总美容师数
            $allStaffNum = Staff::alias('s')
                    ->join('staff_bind_store b', 'b.staff_id = s.id', 'left')
                    ->join('store t', 't.id = b.store_id', 'left')
                    ->where('t.id', 'in', $store_ids)
                    ->count();
            $allBossList[$k]['allStaffNum'] = $allStaffNum;
            //新增消费用户数
            $sql = "SELECT COUNT(a.id) num FROM
                    (
                    SELECT
                        MIN(id) id,MIN(pay_time) pay_time,
                        ctm_id
                    FROM
                        ai_order
                    WHERE
                        status = 2 AND store_id in(" . $store_ids . ")
                    GROUP BY
                        ctm_id
                ) AS a
                WHERE
                    a.pay_time BETWEEN '" . $start . "' AND '" . $end . "'";
            $res = Db::query($sql);
            $allBossList[$k]['newBuyCtmNum'] = $res[0]['num'];
            //总消费用户数
            $allBossList[$k]['allBuyCtmNum'] = Order::where('status', 2)
                    ->where('store_id', 'in', $store_ids)
                    ->group('ctm_id')
                    ->count();
            //新增消费单数
            $newOrderNum = Order::where('status', 2)
                    ->where('store_id', 'in', $store_ids)
                    ->where('add_time', 'between', [$start, $end])
                    ->count();
            $newGoodsOrderNum = Db::table('pay_order')
                    ->where('status', '>', 1)
                    ->where('store_id', 'in', $store_ids)
                    ->where('create_time', 'between', [$start, $end])
                    ->count();
            $allBossList[$k]['newOrderNum'] = $newOrderNum;
            $allBossList[$k]['newGoodsOrderNum'] = $newGoodsOrderNum; //新增商品消费单数
            //总消费单数
            $allOrderNum = Order::where('status', 2)
                    ->where('store_id', 'in', $store_ids)
                    ->count();
            $allGoodsOrderNum = Db::table('pay_order')
                    ->where('status', '>', 1)
                    ->where('store_id', 'in', $store_ids)
                    ->count();
            $allBossList[$k]['allOrderNum'] = $allOrderNum;
            $allBossList[$k]['allGoodsOrderNum'] = $allGoodsOrderNum;
            //新增收入
            $newOrderMoney = Order::where('status', 2)
                    ->where('store_id', 'in', $store_ids)
                    ->where('add_time', 'between', [$start, $end])
                    ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                    ->find();
            $newGoodsOrderMoney = Db::table('pay_order')
                    ->where('status', '>', 1)
                    ->where('store_id', 'in', $store_ids)
                    ->where('create_time', 'between', [$start, $end])
                    ->sum('pay_money');
            $allBossList[$k]['newOrderMoney'] = $newOrderMoney['pay_money'] + $newOrderMoney['balance_money'];
            $allBossList[$k]['newGoodsOrderMoney'] = $newGoodsOrderMoney;
            //总收入
            $allOrderMoney = Order::where('status', 2)
                    ->where('store_id', 'in', $store_ids)
                    ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                    ->find();
            $allGoodsOrderMoney = Db::table('pay_order')
                    ->where('status', '>', 1)
                    ->where('store_id', 'in', $store_ids)
                    ->sum('pay_money');
            $allBossList[$k]['allOrderMoney'] = $allOrderMoney['pay_money'] + $allOrderMoney['balance_money'];
            $allBossList[$k]['allGoodsOrderMoney'] = $allGoodsOrderMoney;
        }
        return $this->ajaxReturn(0, '查询成功', $allBossList);
    }
    
    /*
     * 美容师管理
     * author xfwed 
     * date 2020.08.20
     */

    public function staffIndex() {
        $agency_id = input('param.agency_id'); //代理id
        $dateType = input('param.dateType'); //1昨日 2上周 3上月
        $staff_type = input('param.staff_type');//美容师类型 0全部 1品牌连锁店 2服务授权店
        //
        $perPage = input('param.perPage');
        $page = input('param.page');
        //今天
        $today = date("Y-m-d");
        //昨天
        $yesterday_start = date("Y-m-d 00:00:00", strtotime("-1 day"));
        $yesterday_end = date("Y-m-d 23:59:59", strtotime("-1 day"));
        //上周
        $lastweek_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7, date("Y")));
        $lastweek_end = date("Y-m-d H:i:s", mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7, date("Y")));
        //上月
        $beginTime1 = date('Y-m-01 00:00:00', strtotime('-1 month')); //上月开始时间
        $endTime1 = date("Y-m-d 23:59:59", strtotime(-date('d') . 'day')); //上月结束时间

        if ($dateType == 1) {
            $start = $yesterday_start;
            $end = $yesterday_end;
        } elseif ($dateType == 2) {
            $start = $lastweek_start;
            $end = $lastweek_end;
        } elseif ($dateType == 3) {
            $start = $beginTime1;
            $end = $endTime1;
        }
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        if($staff_type){
            $where['s.type'] = $staff_type;
        }
        $where['s.staff_on'] = 1;
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $allStaffList = StaffBindStore::alias('sbs')
                ->join('staff s','s.id = sbs.staff_id','left')
                ->where('sbs.store_id','in',$store_ids)
                ->where($where)
                ->field('s.id,s.staff_name,s.telephone,s.type staff_type')
                ->group('sbs.staff_id')
                ->paginate($perPage);
        foreach ($allStaffList as $k => $v) {
            //归属门店
            $allStaffList[$k]['staffStore'] = StaffBindStore::alias('sbs')
                    ->join('store s','s.id = sbs.store_id','left')
                    ->where('sbs.staff_id',$v['id'])
                    ->column('s.store_name');
            //新获取用户数
            $sql = "SELECT COUNT(a.id) num FROM
                    (
                    SELECT
                        MIN(id) id,
                        MIN(add_time) add_time,
                        ctm_id
                    FROM
                        ai_staff_ctm
                    WHERE staff_id = ".$v['id']."
                GROUP BY
                    ctm_id
                ) AS a
                WHERE
                    a.add_time BETWEEN '" . $start . "' AND '" . $end . "';";
            $res = Db::query($sql);
            $allStaffList[$k]['newCtmNum'] = $res[0]['num'];
            //总获取用户数
            $allStaffList[$k]['allNewCtm'] = StaffCtm::where('staff_id',$v['id'])->group('ctm_id')->count();
            //新增接单数
            $allStaffList[$k]['newYuyue'] = Yuyue::where('staff_id',$v['id'])
                    ->whereNotNull('confirm_time')
                    ->where('store_id','in',$store_ids)
                    ->where('confirm_time','between',[$start,$end])
                    ->count();
            //总接单数
            $allStaffList[$k]['allYuyue'] = Yuyue::where('staff_id',$v['id'])
                    ->whereNotNull('confirm_time')
                    ->where('store_id','in',$store_ids)
                    ->count();
            //新增消费用户数
            $sql = "SELECT COUNT(a.id) num FROM
                    (
                    SELECT
                        MIN(id) id,MIN(pay_time) pay_time,
                        ctm_id
                    FROM
                        ai_order
                    WHERE
                        status = 2 AND store_id in(" . $store_ids . ") AND staff_id = ".$v['id']."
                    GROUP BY
                        ctm_id
                ) AS a
                WHERE
                    a.pay_time BETWEEN '" . $start . "' AND '" . $end . "'";
            $res = Db::query($sql);
            $allStaffList[$k]['newBuyCtmNum'] = $res[0]['num'];
            //总消费用户数
            $allStaffList[$k]['allBuyCtmNum'] = Order::where('status', 2)
                    ->where('store_id', 'in', $store_ids)
                    ->where('staff_id',$v['id'])
                    ->group('ctm_id')
                    ->count();
            //新增订单金额
            $allStaffList[$k]['newOrderMoney'] = Yuyue::where('staff_id',$v['id'])
                    ->whereNotNull('confirm_time')
                    ->where('store_id','in',$store_ids)
                    ->where('confirm_time','between',[$start,$end])
                    ->sum('money');
            //总订单金额（未服务/已服务）
            $allStaffList[$k]['allOrderMoney'] = Yuyue::where('staff_id',$v['id'])
                    ->whereNotNull('confirm_time')
                    ->where('store_id','in',$store_ids)
                    ->sum('money');
            //总服务次数
            $allStaffList[$k]['allSerNum'] = Yuyue::where('staff_id',$v['id'])
                    ->where('store_id','in',$store_ids)
                    ->where('status','in','4,7,8')
                    ->count();
            //总服务人数
            $allStaffList[$k]['allSerctm'] = Yuyue::where('staff_id',$v['id'])
                    ->where('store_id','in',$store_ids)
                    ->where('status','in','4,7,8')
                    ->group('ctm_id')
                    ->count();
        }
        return $this->ajaxReturn(0, '查询成功', $allStaffList);
    }
    
    /*
     * 门店管理
     * author xfwed 
     * date 2020.08.24
     */

    public function storeIndex() {
        $agency_id = input('param.agency_id'); //代理id
        $dateType = input('param.dateType'); //1昨日 2上周 3上月
        $store_name = input('param.store_name');//店铺名称
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店
        //
        $perPage = input('param.perPage');
        $page = input('param.page');
        //今天
        $today = date("Y-m-d");
        //昨天
        $yesterday_start = date("Y-m-d 00:00:00", strtotime("-1 day"));
        $yesterday_end = date("Y-m-d 23:59:59", strtotime("-1 day"));
        //上周
        $lastweek_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7, date("Y")));
        $lastweek_end = date("Y-m-d H:i:s", mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7, date("Y")));
        //上月
        $beginTime1 = date('Y-m-01 00:00:00', strtotime('-1 month')); //上月开始时间
        $endTime1 = date("Y-m-d 23:59:59", strtotime(-date('d') . 'day')); //上月结束时间

        if ($dateType == 1) {
            $start = $yesterday_start;
            $end = $yesterday_end;
        } elseif ($dateType == 2) {
            $start = $lastweek_start;
            $end = $lastweek_end;
        } elseif ($dateType == 3) {
            $start = $beginTime1;
            $end = $endTime1;
        }
        if($store_name){
            $where['s.store_name'] = ['like','%'.$store_name.'%'];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $where['s.agency_id'] = $agency_id;
        
        $allStoreList = Store::alias('s')
                ->join('store_staff ss','ss.store_id = s.id','left')
                ->join('staff st','st.id = ss.staff_id','left')
                ->where($where)
                ->field('s.id,s.store_name,s.store_address,s.type store_type,st.staff_name')
                ->paginate($perPage);
                
        foreach ($allStoreList as $k => $v) {
            //新增顾客及来源（占比）
            $sql = "SELECT id FROM (
                    SELECT MIN(id) id,MIN(add_time) add_time,ctm_id,store_id
                    FROM ai_store_ctm
                    WHERE store_id =" . $v['id'] . " GROUP BY ctm_id
                ) AS a
                WHERE 1=1
                ";
            $res = Db::query($sql);
            foreach ($res as $kk => $vv) {
                $ids6[] = $vv['id'];
            }

//            $newCtmNum = StoreCtm::alias('s')
//                    ->where('s.add_time', 'between', [$start, $end])
//                    ->where('s.id', 'in', $ids6)
//                    ->order('s.add_time asc')
//                    ->count();
//            $allStoreList[$k]['newCtmNum'] = $newCtmNum;
            //总顾客及来源（占比）
//            $allCtmNum = StoreCtm::where('id', 'in', $ids6)
//                    ->where('store_id', '=', $v['id'])
//                    ->order('add_time asc')
//                    ->count();
            $allStoreList[$k]['allCtmNum'] = count($ids6);
            //新增美容师数
//            $newStaffNum = Staff::alias('s')
//                    ->join('staff_bind_store b', 'b.staff_id = s.id', 'left')
//                    ->join('store t', 't.id = b.store_id', 'left')
//                    ->where('b.add_time', 'between', [$start, $end])
//                    ->where('t.id', '=', $v['id'])
//                    ->count();
//            $allStoreList[$k]['newStaffNum'] = $newStaffNum;
            //总美容师数
            $allStaffNum = Staff::alias('s')
                    ->join('staff_bind_store b', 'b.staff_id = s.id', 'left')
                    ->join('store t', 't.id = b.store_id', 'left')
                    ->where('t.id', '=', $v['id'])
                    ->count();
            $allStoreList[$k]['allStaffNum'] = $allStaffNum;
            //新增消费用户数
//            $sql = "SELECT COUNT(a.id) num FROM
//                    (
//                    SELECT
//                        MIN(id) id,MIN(pay_time) pay_time,
//                        ctm_id
//                    FROM
//                        ai_order
//                    WHERE
//                        status = 2 AND store_id =" . $v['id'] . "
//                    GROUP BY
//                        ctm_id
//                ) AS a
//                WHERE
//                    a.pay_time BETWEEN '" . $start . "' AND '" . $end . "'";
//            $res = Db::query($sql);
//            $allStoreList[$k]['newBuyCtmNum'] = $res[0]['num'];
            //总消费用户数
            $allStoreList[$k]['allBuyCtmNum'] = Order::where('status', 2)
                    ->where('store_id', '=', $v['id'])
                    ->group('ctm_id')
                    ->count();
            //新增消费单数
//            $newOrderNum = Order::where('status', 2)
//                    ->where('store_id', '=', $v['id'])
//                    ->where('add_time', 'between', [$start, $end])
//                    ->count();
//            $newGoodsOrderNum = Db::table('pay_order')
//                    ->where('status', '>', 1)
//                    ->where('store_id', '=', $v['id'])
//                    ->where('create_time', 'between', [$start, $end])
//                    ->count();
//            $allStoreList[$k]['newOrderNum'] = $newOrderNum;
//            $allStoreList[$k]['newGoodsOrderNum'] = $newGoodsOrderNum; //新增商品消费单数
            //总消费单数
            $allOrderNum = Order::where('status', 2)
                    ->where('store_id', '=', $v['id'])
                    ->count();
            $allGoodsOrderNum = Db::table('pay_order')
                    ->where('status', '>', 1)
                    ->where('store_id', '=', $v['id'])
                    ->count();
            $allStoreList[$k]['allOrderNum'] = $allOrderNum;
            $allStoreList[$k]['allGoodsOrderNum'] = $allGoodsOrderNum;
            //新增收入
//            $newOrderMoney = Order::where('status', 2)
//                    ->where('store_id', '=', $v['id'])
//                    ->where('add_time', 'between', [$start, $end])
//                    ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
//                    ->find();
//            $newGoodsOrderMoney = Db::table('pay_order')
//                    ->where('status', '>', 1)
//                    ->where('store_id', '=', $v['id'])
//                    ->where('create_time', 'between', [$start, $end])
//                    ->sum('pay_money');
//            $allStoreList[$k]['newOrderMoney'] = $newOrderMoney['pay_money'] + $newOrderMoney['balance_money'];
//            $allStoreList[$k]['newGoodsOrderMoney'] = $newGoodsOrderMoney;
            //总收入
            $allOrderMoney = Order::where('status', 2)
                    ->where('store_id', '=', $v['id'])
                    ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                    ->find();
            $allGoodsOrderMoney = Db::table('pay_order')
                    ->where('status', '>', 1)
                    ->where('store_id', '=', $v['id'])
                    ->sum('pay_money');
            $allStoreList[$k]['allOrderMoney'] = $allOrderMoney['pay_money'] + $allOrderMoney['balance_money'];
            $allStoreList[$k]['allGoodsOrderMoney'] = $allGoodsOrderMoney;
        }
        return $this->ajaxReturn(0, '查询成功', $allStoreList);
    }
    
    /*
     * 消费管理
     * author xfwed 
     * date 2020.08.31
     */

    public function consumeIndex() {
        $agency_id = input('param.agency_id'); //代理id
        $type = input('param.type');//消费类别  类型 0全部（不包括商品）1项目 2超值组合 3充值 4 特权卡5秒杀6体验特权卡 79.9新人 8商品	
        $from = input('param.from');//消费来源  0全部1云小店，2美容师推广，3老板推广
        $dateType = input('param.dateType'); //1昨日 2上周 3上月
        //
        $perPage = input('param.perPage');
        $page = input('param.page');
        //今天
        $today = date("Y-m-d");
        //昨天
        $yesterday_start = date("Y-m-d 00:00:00", strtotime("-1 day"));
        $yesterday_end = date("Y-m-d 23:59:59", strtotime("-1 day"));
        //上周
        $lastweek_start = date("Y-m-d H:i:s", mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7, date("Y")));
        $lastweek_end = date("Y-m-d H:i:s", mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7, date("Y")));
        //上月
        $beginTime1 = date('Y-m-01 00:00:00', strtotime('-1 month')); //上月开始时间
        $endTime1 = date("Y-m-d 23:59:59", strtotime(-date('d') . 'day')); //上月结束时间

        if ($dateType == 1) {
            $start = $yesterday_start;
            $end = $yesterday_end;
        } elseif ($dateType == 2) {
            $start = $lastweek_start;
            $end = $lastweek_end;
        } elseif ($dateType == 3) {
            $start = $beginTime1;
            $end = $endTime1;
        }
        
        if($type && $type!=8){
            if($type == 1){//类型 1项目 2超值组合 3充值 4 特权卡5秒杀6体验特权卡 79.9新人 8商品
                $where['type'] = 1;
                $where['first_exemption'] = ['<>',2];
            }elseif ($type == 7) {
                $where['type'] = 1;
                $where['first_exemption'] = 2;
            }  else {
                $where['type'] = $type;
            }
            
        }
        if($from == 1){//消费来源  0全部1云小店，2美容师推广，3老板推广
            $where['mini_shop_id'] = ['<>',0];
        }elseif ($from == 2) {
            $where['staff_id'] = ['<>',0];
        }elseif ($from == 3) {
            $where['store_id'] = ['<>',0];
        }
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $where['store_id'] = ['in',$store_ids];
        $where['status'] = 2;
        
        if($type != 8){//项目
            $money = Order::where($where)
                    ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                    ->select();
            $allMoney = $money[0]['pay_money'] + $money[0]['balance_money'];
            $where['pay_time'] = ['between',[$start,$end]];
            $list = Order::where($where)
                    ->field('id,type,store_id,staff_id,ord_num,first_exemption,mini_shop_id,pay_time,pay_money,balance_money')
                    ->paginate($perPage);
            foreach ($list as $k => $v){//类型 1项目 2超值组合 3充值 4 特权卡5秒杀6体验特权卡 79.9新人 8商品
                if($v['type'] == 1 && $v['first_exemption']!=2){
                   $list[$k]['typeName'] = '项目'; 
                }elseif ($v['type'] == 2) {
                    $list[$k]['typeName'] = '超值组合'; 
                }elseif ($v['type'] == 3) {
                    $list[$k]['typeName'] = '余额充值'; 
                }elseif ($v['type'] == 4) {
                    $list[$k]['typeName'] = '特权卡'; 
                }elseif ($v['type'] == 5) {
                    $list[$k]['typeName'] = '秒杀'; 
                }elseif ($v['type'] == 6) {
                    $list[$k]['typeName'] = '体验特权卡'; 
                }elseif ($v['type'] == 1 && $v['first_exemption']==2) {
                    $list[$k]['typeName'] = '9.9新人'; 
                }elseif ($v['type'] == 7) {
                    $list[$k]['typeName'] = '医美项目'; 
                }
                if($v['mini_shop_id']!=0){//消费来源  0全部1云小店，2美容师推广，3老板推广
                    $list[$k]['fromName'] = 'i美优优小店';
                }elseif ($v['staff_id'] != 0) {
                    $list[$k]['fromName'] = '美容师推广';
                }elseif ($v['store_id'] != 0) {
                    $list[$k]['fromName'] = '店铺推广';
                }
                    
            }
        }  else {//商品
            $where['status'] = ['>',1];
            $money = Db::table('pay_order')
                    ->where($where)
                    ->field('sum(pay_money) pay_money,sum(balance_money) balance_money')
                    ->paginate($perPage);
            $allMoney = $money[0]['pay_money'] + $money[0]['balance_money'];
            $list = Db::table('pay_order')
                    ->where($where)
                    ->field('id,store_id,staff_id,order_id ord_num,mini_shop_id,pay_time,pay_money,balance_money')
                    ->paginate($perPage)->toArray();
//    print_r($list);die;
            foreach ($list['data'] as $k => $v){
                $list['data'][$k]['typeName'] = '商品'; 
                
                if($v['mini_shop_id']!=0){//消费来源  0全部1云小店，2美容师推广，3老板推广
                    $list['data'][$k]['fromName'] = 'i美优优小店';
                }elseif ($v['staff_id'] != 0) {
                    $list['data'][$k]['fromName'] = '美容师推广';
                }elseif ($v['store_id'] != 0) {
                    $list['data'][$k]['fromName'] = '店铺推广';
                }
                    
            }
        }
       $data['allMoney'] = $allMoney;
       $data['list'] = $list;
       return $this->ajaxReturn(0, '查询成功', $data);
    }
    
    /*
     * 项目项次
     * author xfwed 
     * date 2020.09.14
     */

    public function yuYueList() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $staff_id = input('param.staff_id');//选择的美容师
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['y.staff_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $where['y.store_id'] = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            $where['y.store_id'] = ['in',$store_ids];
        }
        if($staff_id){
            $where['y.staff_id'] = $staff_id;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $where['y.status'] = ['in','4,7,8'];
        $yuyueList = Yuyue::alias('y')
                ->join('store s','s.id = y.store_id','left')
                ->join('staff st','st.id = y.staff_id','left')
                ->join('custom c','c.id = y.ctm_id','left')
                ->join('item i','i.id = y.item_id','left')
                ->where($where)
                ->field('y.id,y.staff_time,y.money,s.store_name,st.staff_name,c.custom_name,i.item_name')
                ->paginate($perPage);
       
        return $this->ajaxReturn(0, '查询成功', $yuyueList);
    }
    
    /*
     * 99特权
     * author xfwed 
     * date 2020.09.14
     */

    public function specialList() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $staff_name = input('param.staff_name');//美容师姓名
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['o.pay_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $where['o.store_id'] = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            $where['o.store_id'] = ['in',$store_ids];
        }
        
        if($staff_name){
            $where['st.staff_name'] = ['like','%'.$staff_name.'%'];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $where['o.status'] = 2;
        $where['o.type'] = 4;
        
        $list = Order::alias('o')
                ->join('custom c','c.id = o.ctm_id','left')
                ->join('store s','s.id = o.store_id','left')
                ->join('staff st','st.id = c.exclusive_staff_id','left')
                ->where($where)
                ->field('o.id,o.pay_time,o.pay_money,o.balance_money,o.red_money,o.is_shop_open,s.store_name,s.type store_type,c.custom_name,st.staff_name')
                ->paginate($perPage);

        return $this->ajaxReturn(0, '查询成功', $list);
    }
    
    /*
     * 店铺新客
     * author xfwed 
     * date 2020.09.14
     */

    public function storeNewCtmList() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['s.add_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $store_ids = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
        }
        
        if ($startTime && $endTime) {
            $begin = $startTime;
            $end = $endTime;
            
        } else{
            $begin = '1993-01-01';
            $end = '2060-01-01';
        }
        
        if($store_type){
            $where['st.type'] = $store_type;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        
        $sql = "SELECT id FROM (
            SELECT MIN(id) id,MIN(add_time) add_time,ctm_id,store_id
            FROM ai_store_ctm
            WHERE store_id in(" . $store_ids . ") GROUP BY ctm_id,store_id
        ) AS a
        WHERE 1=1
        AND a.add_time >= '".$begin."' AND a.add_time <= '".$end."'
        ";
        $res = Db::query($sql);
        foreach ($res as $k => $v) {
            $ids6[] = $v['id'];
        }
        $where = isset($where) ? $where : [];
        $ids6 = isset($ids6) ? $ids6 : [];
        $allList = StoreCtm::alias('s')
                ->join('store st','st.id = s.store_id','left')
                ->where($where)
                ->where('s.id', 'in', $ids6)
                ->order('s.add_time desc')
                ->field('s.ctm_id,s.add_time,st.store_name,st.type store_type')
                ->paginate($perPage);

        foreach ($allList as $k => $v) {
            $ctmRow = Custom::where('id', $v['ctm_id'])
                    ->field('id,custom_phone,custom_name,custom_photo,vip_status,is_smallshop,source_type,source_id')
                    ->find();
            if ($ctmRow['vip_status'] == 2 || $ctmRow['vip_status'] == 3) {
                $specialTime = CtmSpecial::where('ctm_id', $ctmRow['id'])->order('id asc')->find();
                if ($specialTime['type'] == 3) {
                    $allList[$k]['vip_status'] = 4; //4逆行者特权 5i美优优特权 
                } else {
                    $specialOrder = Order::alias('o')
                            ->where('o.ctm_id', '=', $ctmRow['id'])
                            ->where('o.status', 2)
                            ->where('o.type', 4)
                            ->where('o.mini_shop_id', '<>', 0)
                            ->group('ctm_id')
                            ->count();
                    if ($specialOrder > 0) {
                        $allList[$k]['vip_status'] = 5; //4逆行者特权 5i美优优特权 
                    }  else {
                        $allList[$k]['vip_status'] = $ctmRow['vip_status'];
                    }
                }
            }  else {
                $allList[$k]['vip_status'] = $ctmRow['vip_status'];
            }

            if ($ctmRow['source_type'] == 0) {
                $sourceName = '';
            } elseif ($ctmRow['source_type'] == 1) {
                $sourceName = '平台引流';
            } elseif ($ctmRow['source_type'] == 2) {
                $staffName = Staff::where('id', $ctmRow['source_id'])->value('staff_name');
                $sourceName = '美容师' . $staffName;
            } elseif ($ctmRow['source_type'] == 3) {
                $StoreRow = Store::where('id', $ctmRow['source_id'])->field('store_name,city')->find();
                $cityName = ProvCityArea::where('id', $StoreRow['city'])->value('name');
                $sourceName = $cityName . '的' . $StoreRow['store_name'];
            } elseif ($ctmRow['source_type'] == 4) {
                $sourceName = '小店转介绍';
            }
            $allList[$k]['sourceName'] = $sourceName;
            $allList[$k]['is_smallshop'] = $ctmRow['is_smallshop'];
            $allList[$k]['custom_name'] = $ctmRow['custom_name'];
            $allList[$k]['custom_photo'] = $ctmRow['custom_photo'];
            //$allList[$k]['vip_status'] = $ctmRow['vip_status'];
            $pay_time = Order::where('ctm_id', $v['ctm_id'])
                    ->where('status', 2)
                    //->where('pay_money', '>', 0)
                    //->where('store_id',$store_id)
                    ->order('pay_time desc')
                    ->field('pay_time,pay_money,balance_money')
                    ->find();
            $allList[$k]['pay_time'] = $pay_time['pay_time'];
            $pay_money = Order::where('ctm_id', $v['ctm_id'])->where('status', 2)->sum('pay_money');
            $balance_money = Order::where('ctm_id', $v['ctm_id'])->where('status', 2)->sum('balance_money');
            $allList[$k]['pay_money'] = $pay_money + $balance_money;
        }

        return $this->ajaxReturn(0, '查询成功', $allList);
    }
    
    
    /*
     * 美容师佣金
     * author xfwed 
     * date 2020.09.14
     */

    public function staffFlowList() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //服务时间 开始时间
        $endTime = input('param.endTime'); //服务时间 结束时间
        
        $startUpdateTime = input('param.startUpdateTime');//入账开始时间
        $endUpdateTime = input('param.endUpdateTime');//入账结束时间
        $store_id = input('param.store_id');//选择的店铺
        $type = input('param.type');//1项目2商品3特权4医美5体验卡
        $staff_id = input('param.staff_id');//选择的美容师
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        $staff_type = input('param.staff_type');//美容师类型 0全部 1品牌连锁店 2服务授权店
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['f.add_time'] = ['between',[$startTime,$endTime]];
        }
        
        if($startUpdateTime && $endUpdateTime){
            $where['f.update_time'] = ['between',[$startUpdateTime,$endUpdateTime]];
        }
        if($store_id){
            $store_ids = $store_id;
            if($type == 3 || $type == 4){
                $where['o.store_id'] = $store_id;
            }  elseif($type == 1 || $type == 2) {
                $where['y.store_id'] = $store_id;
            }
            
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            if($type == 3 || $type == 4){
                $where['o.store_id'] = ['in',$store_ids];
            }  elseif($type == 1 || $type == 2) {
                $where['y.store_id'] = ['in',$store_ids];
            }
            
        }
        if($staff_id){
            $where['f.staff_id'] = $staff_id;
        }
        
        if($store_type && $type!=5){
            $where['s.type'] = $store_type;
        }

        if($staff_type){
            $where['st.type'] = $staff_type;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        
        if($type == 1){
            $where['f.type'] = 1;
            $where['f.pro_type'] = ['in','1,2,3,5,16'];
            $where['f.status'] = 2;
            
            $money = StaffFlowNew::alias('f')
                    ->join('yuyue y','y.id = f.order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('staff st2','st2.id = y.staff_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->where($where)
                    ->sum('f.money');
            
            $subsidy_money = StaffFlowNew::alias('f')
                    ->join('yuyue y','y.id = f.order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('staff st2','st2.id = y.staff_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->where($where)
                    ->sum('f.subsidy_money');

            $list = StaffFlowNew::alias('f')
                    ->join('yuyue y','y.id = f.order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('staff st2','st2.id = y.staff_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,s.store_name,s.type store_type,c.custom_name,y.staff_time,st.staff_name,st.type staff_type,st2.staff_name ser_staff_name')
                    ->paginate($perPage);
        }elseif ($type == 2) {
            $where['f.type'] = 1;
            $where['f.pro_type'] = ['in','6,7'];
            $where['f.status'] = 2;
            
            $money = StaffFlowNew::alias('f')
                    ->join(['pay_order_goods' => 'g'],'g.id = f.order_id','left')
                    ->join(['pay_order_detail'=>'y'],'y.id = g.pay_order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->sum('f.money');
            $subsidy_money = StaffFlowNew::alias('f')
                    ->join(['pay_order_goods' => 'g'],'g.id = f.order_id','left')
                    ->join(['pay_order_detail'=>'y'],'y.id = g.pay_order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->sum('f.subsidy_money');

            $list = StaffFlowNew::alias('f')
                    ->join(['pay_order_goods' => 'g'],'g.id = f.order_id','left')
                    ->join(['pay_order_detail'=>'y'],'y.id = g.pay_order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,s.store_name,s.type store_type,c.custom_name,y.pay_time,st.staff_name,st.type staff_type')
                    ->paginate($perPage);
        }elseif($type == 3){
            $where['f.type'] = 1;
            $where['f.pro_type'] = 4;
            $where['f.status'] = 2;
            
            $money = StaffFlowNew::alias('f')
                    ->join('order o','o.id = f.order_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = o.ctm_id','left')
                    ->where($where)
                    ->sum('f.money');
            $subsidy_money = StaffFlowNew::alias('f')
                    ->join('order o','o.id = f.order_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = o.ctm_id','left')
                    ->where($where)
                    ->sum('f.subsidy_money');

            $list = StaffFlowNew::alias('f')
                    ->join('order o','o.id = f.order_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = o.ctm_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,s.store_name,s.type store_type,c.custom_name,o.pay_time,st.staff_name,st.type staff_type')
                    ->paginate($perPage);
        }elseif ($type == 4) {
            $where['f.type'] = 1;
            $where['f.pro_type'] = 15;
            $where['f.status'] = 2;
            
            $money = StaffFlowNew::alias('f')
                    ->join('order o','o.id = f.order_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = f.ctm_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->where($where)
                    ->sum('f.money');
            
            $subsidy_money = StaffFlowNew::alias('f')
                     ->join('order o','o.id = f.order_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = f.ctm_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->where($where)
                    ->sum('f.subsidy_money');

            $list = StaffFlowNew::alias('f')
                     ->join('order o','o.id = f.order_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = f.ctm_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,c.custom_name,st.staff_name,st.type staff_type,o.pay_time,s.store_name,s.type store_type')
                    ->paginate($perPage);
        }elseif ($type == 5) {
            $where['f.type'] = 1;
            $where['f.pro_type'] = ['in','20,21'];
            $where['f.status'] = 2;
            
            $money = StaffFlowNew::alias('f')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->sum('f.money');
            
            $subsidy_money = StaffFlowNew::alias('f')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->sum('f.subsidy_money');

            $list = StaffFlowNew::alias('f')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,f.ctm_name custom_name,st.staff_name,st.type staff_type')
                    ->paginate($perPage);
        }
        $data['money'] = $money;
        $data['subsidy_money'] = $subsidy_money;
        $data['list'] = $list;
        return $this->ajaxReturn(0, '查询成功', $data);
    }
    
    /* 导出美容师佣金
     * author xfwed
     * date 2021-6-18
     */

    public function staffFlowListExcel() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //服务时间 开始时间
        $endTime = input('param.endTime'); //服务时间 结束时间
        
        $startUpdateTime = input('param.startUpdateTime');//入账开始时间
        $endUpdateTime = input('param.endUpdateTime');//入账结束时间
        $store_id = input('param.store_id');//选择的店铺
        $type = input('param.type');//1项目2商品3特权4医美5体验卡
        $staff_id = input('param.staff_id');//选择的美容师
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        $staff_type = input('param.staff_type');//美容师类型 0全部 1品牌连锁店 2服务授权店
        
        if($startTime && $endTime){
            $where['f.add_time'] = ['between',[$startTime,$endTime]];
        }
        
        if($startUpdateTime && $endUpdateTime){
            $where['f.update_time'] = ['between',[$startUpdateTime,$endUpdateTime]];
        }
        if($store_id){
            $store_ids = $store_id;
            if($type == 3 || $type == 4){
                $where['o.store_id'] = $store_id;
            }  elseif($type == 1 || $type == 2) {
                $where['y.store_id'] = $store_id;
            }
            
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            if($type == 3 || $type == 4){
                $where['o.store_id'] = ['in',$store_ids];
            }  elseif($type == 1 || $type == 2) {
                $where['y.store_id'] = ['in',$store_ids];
            }
            
        }
        if($staff_id){
            $where['f.staff_id'] = $staff_id;
        }
        if($store_type && $type!=5){
            $where['s.type'] = $store_type;
        }

        if($staff_type){
            $where['st.type'] = $staff_type;
        }
        
        
        if($type == 1){
            $where['f.type'] = 1;
            $where['f.pro_type'] = ['in','1,2,3,5,16'];
            $where['f.status'] = 2;
            $list = StaffFlowNew::alias('f')
                    ->join('yuyue y','y.id = f.order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('staff st2','st2.id = y.staff_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,s.store_name,c.custom_name,y.staff_time,st.staff_name,st2.staff_name ser_staff_name,s.type store_type,st.type staff_type')
                    ->select();
        }elseif ($type == 2) {
            $where['f.type'] = 1;
            $where['f.pro_type'] = ['in','6,7'];
            $where['f.status'] = 2;

            $list = StaffFlowNew::alias('f')
                    ->join(['pay_order_goods' => 'g'],'g.id = f.order_id','left')
                    ->join(['pay_order_detail'=>'y'],'y.id = g.pay_order_id','left')
                    ->join('store s','s.id = y.store_id','left')
                    ->join('custom c','c.id = y.ctm_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,s.store_name,c.custom_name,y.pay_time,st.staff_name,s.type store_type,st.type staff_type')
                    ->select();
        }elseif($type == 3){
            $where['f.type'] = 1;
            $where['f.pro_type'] = 4;
            $where['f.status'] = 2;
            
            $list = StaffFlowNew::alias('f')
                    ->join('order o','o.id = f.order_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = o.ctm_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,s.store_name,c.custom_name,o.pay_time,st.staff_name,s.type store_type,st.type staff_type')
                    ->select();
        }elseif ($type == 4) {
            $where['f.type'] = 1;
            $where['f.pro_type'] = 15;
            $where['f.status'] = 2;

            $list = StaffFlowNew::alias('f')
                     ->join('order o','o.id = f.order_id','left')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->join('custom c','c.id = f.ctm_id','left')
                    ->join('store s','s.id = o.store_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,c.custom_name,st.staff_name,o.pay_time,s.store_name,s.type store_type,st.type staff_type')
                    ->select();
        }elseif ($type == 5) {
            $where['f.type'] = 1;
            $where['f.pro_type'] = ['in','20,21'];
            $where['f.status'] = 2;

            $list = StaffFlowNew::alias('f')
                    ->join('staff st','st.id = f.staff_id','left')
                    ->where($where)
                    ->order('f.add_time desc')
                    ->field('f.id,f.item_name,f.all_money allmoney,f.money,f.subsidy_money,f.add_time,f.update_time,f.ctm_name custom_name,st.staff_name,s.type store_type,st.type staff_type')
                    ->select();
        }

        $expTableData = $list;


        $expTitle = '美容师佣金';
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle . '(-共计' . count($expTableData) . '条)'); //文件名称
        $fileName = $xlsTitle; //or $xlsTitle 文件名称可根据自己情况设定
        if (empty($expTableData)) {
            return Utils::ajaxReturn(3, '没有相关数据', null);
        }
        $ii = 3;
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[14 - 1] . '1'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(//居中加粗
                array(
                    'font' => array(
                        'bold' => true
                    ),
                    'alignment' => array(
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                    )
                )
        );
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 所有单元格默认高度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); //设置宽width
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框  
                //'color' => array('argb' => 'FFFF0000'),  
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:K' . (count($expTableData) + 2))->applyFromArray($styleArray); //这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代  


        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', "编号");
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "店铺名称");
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "项目名称");
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', "客户姓名");
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', "服务时间");
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', "付款时间");
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', "入账美容师");
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H2', "订单金额");
        $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I2', "入账金额");
        $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J2', "其中包含补贴金额");
        $objPHPExcel->getActiveSheet()->getStyle('J2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K2', "入账时间");
        $objPHPExcel->getActiveSheet()->getStyle('K2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L2', "服务美容师");
        $objPHPExcel->getActiveSheet()->getStyle('L2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M2', "美容师类型");
        $objPHPExcel->getActiveSheet()->getStyle('M2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N2', "店铺类型");
        $objPHPExcel->getActiveSheet()->getStyle('N2')->applyFromArray(array('font' => array('bold' => true)));

        foreach ($expTableData as $key => $val) {
            if($type == 1 || $type == 5){
                $pay_time = '';
            }  else {
                $pay_time = $val['pay_time'];
            }
            
            if($type == 2 || $type == 3 || $type == 4 || $type == 5){
                $staff_time = '';
            }  else {
                $staff_time = $val['staff_time'];
            }
            if($type == 5){
                $store_name = '';
            }  else {
                $store_name = $val['store_name'];
            }
            
            
            $tt = 'A' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['id']); //加  “ ”防止用科学计数法，中间必须有空格，否则无效
            $tt = 'B' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $store_name);
            $tt = 'C' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['item_name']);
            $tt = 'D' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['custom_name']);
            $tt = 'E' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['add_time']);
            $tt = 'F' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $pay_time);
            $tt = 'G' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['staff_name']);
            $tt = 'H' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['allmoney']/100);
            $tt = 'I' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['money']/100);
            $tt = 'J' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['subsidy_money']/100);
            $tt = 'K' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['update_time']);
            $tt = 'L' . $ii;
            if($type == 1){
                $ser_staff_name = $val['ser_staff_name'];
            }  else {
                $ser_staff_name = '无';
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $ser_staff_name);
            $tt = 'M' . $ii;
            if($val['staff_type'] == 1){
                $stn = '品牌连锁店美容师';
            }elseif ($val['staff_type'] == 2) {
                $stn = '服务授权店美容师';
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $stn);
            $tt = 'N' . $ii;
            if($val['staff_type'] == 1){
                $tn = '品牌连锁店';
            }elseif ($val['staff_type'] == 2) {
                $tn = '服务授权店';
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $tn);
            $ii++;
        }
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Pragma: no-cache");
        header("Expires: 0");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    
    
    
    /* 导出项目项次
     * author xfwed
     * date 2020-10-15
     */

    public function yuYueListExcel() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $staff_id = input('param.staff_id');//选择的美容师
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['y.staff_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $where['y.store_id'] = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            $where['y.store_id'] = ['in',$store_ids];
        }
        if($staff_id){
            $where['y.staff_id'] = $staff_id;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $where['y.status'] = ['in','4,7,8'];
        $expTableData = Yuyue::alias('y')
                ->join('store s','s.id = y.store_id','left')
                ->join('staff st','st.id = y.staff_id','left')
                ->join('custom c','c.id = y.ctm_id','left')
                ->join('item i','i.id = y.item_id','left')
                ->where($where)
                ->field('y.id,y.staff_time,y.money,s.store_name,st.staff_name,c.custom_name,i.item_name')
                ->select();

        $expTitle = '项目项次明细';
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle . '(-共计' . count($expTableData) . '条)'); //文件名称
        $fileName = $xlsTitle; //or $xlsTitle 文件名称可根据自己情况设定
        if (empty($expTableData)) {
            Utils::ajaxReturn(3, '没有相关数据', null);
        }
        $ii = 3;
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[6 - 1] . '1'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(//居中加粗
                array(
                    'font' => array(
                        'bold' => true
                    ),
                    'alignment' => array(
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                    )
                )
        );
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 所有单元格默认高度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); //设置宽width
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框  
                //'color' => array('argb' => 'FFFF0000'),  
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:F' . (count($expTableData) + 2))->applyFromArray($styleArray); //这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代  


        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', "店铺名称");
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "美容师姓名");
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "客户姓名");
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', "项目名称");
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', "项目金额");
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', "服务时间");
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray(array('font' => array('bold' => true)));

        foreach ($expTableData as $key => $val) {
            $tt = 'A' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['store_name']); //加  “ ”防止用科学计数法，中间必须有空格，否则无效
            $tt = 'B' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['staff_name']);
            $tt = 'C' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['custom_name']);
            $tt = 'D' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['item_name']);
            $tt = 'E' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['money']/100);
            $tt = 'F' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['staff_time']);
            $ii++;
        }
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Pragma: no-cache");
        header("Expires: 0");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    
    /* 导出99特权
     * author xfwed
     * date 2020-10-15
     */

    public function specialListExcel() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $staff_name = input('param.staff_name');//美容师姓名
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['o.pay_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $where['o.store_id'] = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            $where['o.store_id'] = ['in',$store_ids];
        }
        if($staff_name){
            $where['st.staff_name'] = ['like','%'.$staff_name.'%'];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $where['o.status'] = 2;
        $where['o.type'] = 4;
        
        $expTableData = Order::alias('o')
                ->join('custom c','c.id = o.ctm_id','left')
                ->join('store s','s.id = o.store_id','left')
                ->join('staff st','st.id = c.exclusive_staff_id','left')
                ->where($where)
                ->field('o.id,o.pay_time,o.is_shop_open,o.pay_money,o.balance_money,o.red_money,s.store_name,s.type store_type,c.custom_name,st.staff_name')
                ->select();

        $expTitle = '99特权明细';
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle . '(-共计' . count($expTableData) . '条)'); //文件名称
        $fileName = $xlsTitle; //or $xlsTitle 文件名称可根据自己情况设定
        if (empty($expTableData)) {
            Utils::ajaxReturn(3, '没有相关数据', null);
        }
        $ii = 3;
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[7 - 1] . '1'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(//居中加粗
                array(
                    'font' => array(
                        'bold' => true
                    ),
                    'alignment' => array(
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                    )
                )
        );
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 所有单元格默认高度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); //设置宽width
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框  
                //'color' => array('argb' => 'FFFF0000'),  
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:D' . (count($expTableData) + 2))->applyFromArray($styleArray); //这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代  


        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', "店铺名称");
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "客户姓名");
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "开通渠道");
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', "开通时间");
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', "专属美容师");
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', "实付金额");
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G2', "可提现付款");
        $objPHPExcel->getActiveSheet()->getStyle('G2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H2', "红包金额");
        $objPHPExcel->getActiveSheet()->getStyle('H2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I2', "店铺类型");
        $objPHPExcel->getActiveSheet()->getStyle('I2')->applyFromArray(array('font' => array('bold' => true)));

        foreach ($expTableData as $key => $val) {
            $tt = 'A' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['store_name']); //加  “ ”防止用科学计数法，中间必须有空格，否则无效
            $tt = 'B' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['custom_name']);
            $tt = 'C' . $ii;
            if($val['is_shop_open'] == 1){
                $name = 'i美优优';
            }  else {
                $name = '店铺';
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $name);
            $tt = 'D' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['pay_time']);
            $tt = 'E' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['staff_name']);
            $tt = 'F' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['pay_money']/100);
            $tt = 'G' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['balance_money']/100);
            $tt = 'H' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['red_money']/100);
            $tt = 'I' . $ii;
            if($val['store_type'] == 1){
                $stn = '品牌连锁店';
            }elseif ($val['store_type'] == 2) {
                $stn = '服务授权店';
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $stn);
            $ii++;
        }
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Pragma: no-cache");
        header("Expires: 0");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    
    /*
     * 新人9.9订单
     * 日期 2020-10-16
     */

    public function newOrderList() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店
        
        $perPage = input('param.perPage');
        $page = input('param.page');
        if($startTime && $endTime){
            $where['o.pay_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $where['o.store_id'] = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            $where['o.store_id'] = ['in',$store_ids];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        $where['o.status'] = 2;
        $where['o.first_exemption'] = 2;
        
        $list = Order::alias('o')
                ->join('custom c','c.id = o.ctm_id','left')
                ->join('store s','s.id = o.store_id','left')
                ->where($where)
                ->field('o.id,o.ord_num,o.pay_time,s.store_name,s.type store_type,c.custom_name,c.custom_phone')
                ->paginate($perPage);
        return $this->ajaxReturn(0, '查询成功', $list);
    }
    
    /* 导出新人9.9订单
     * author xfwed
     * date 2020-10-16
     */

    public function newOrderListExcel() {
        $agency_id = input('param.agency_id'); //代理id
        $startTime = input('param.startTime'); //开始时间
        $endTime = input('param.endTime'); //结束时间
        $store_id = input('param.store_id');//选择的店铺
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店
       
        if($startTime && $endTime){
            $where['o.pay_time'] = ['between',[$startTime,$endTime]];
        }
        if($store_id){
            $where['o.store_id'] = $store_id;
        }  else {
            $store_ids = Store::where('agency_id',$agency_id)->column('id');
            $store_ids = implode(',', $store_ids);
            $where['o.store_id'] = ['in',$store_ids];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }

        $where['o.status'] = 2;
        $where['o.first_exemption'] = 2;
        
        $expTableData = Order::alias('o')
                ->join('custom c','c.id = o.ctm_id','left')
                ->join('store s','s.id = o.store_id','left')
                ->where($where)
                ->field('o.id,o.ord_num,o.pay_time,s.store_name,c.custom_name,c.custom_phone,s.type store_type')
                ->select();

        $expTitle = '新人9.9订单明细';
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle . '(-共计' . count($expTableData) . '条)'); //文件名称
        $fileName = $xlsTitle; //or $xlsTitle 文件名称可根据自己情况设定
        if (empty($expTableData)) {
            Utils::ajaxReturn(3, '没有相关数据', null);
        }
        $ii = 3;
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[6 - 1] . '1'); //合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(//居中加粗
                array(
                    'font' => array(
                        'bold' => true
                    ),
                    'alignment' => array(
                        'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                    )
                )
        );
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(25); // 第一行的默认高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); // 所有单元格默认高度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); //设置宽width
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20); //设置宽width
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框  
                //'color' => array('argb' => 'FFFF0000'),  
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:E' . (count($expTableData) + 2))->applyFromArray($styleArray); //这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代  


        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', "订单id");
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B2', "订单编号");
        $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C2', "店铺名称");
        $objPHPExcel->getActiveSheet()->getStyle('C2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D2', "客户姓名");
        $objPHPExcel->getActiveSheet()->getStyle('D2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E2', "支付时间");
        $objPHPExcel->getActiveSheet()->getStyle('E2')->applyFromArray(array('font' => array('bold' => true)));
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F2', "店铺类型");
        $objPHPExcel->getActiveSheet()->getStyle('F2')->applyFromArray(array('font' => array('bold' => true)));

        foreach ($expTableData as $key => $val) {
            $tt = 'A' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['id']); //加  “ ”防止用科学计数法，中间必须有空格，否则无效
            $tt = 'B' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, " " . $val['ord_num']);
            $tt = 'C' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['store_name']);
            $tt = 'D' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['custom_name']);
            $tt = 'E' . $ii;
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $val['pay_time']);
            $tt = 'F' . $ii;
            if($val['store_type'] == 1){
                $stn = '品牌连锁店';
            }elseif ($val['store_type'] == 2) {
                $stn = '服务授权店';
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($tt, $stn);
            $ii++;
        }
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Pragma: no-cache");
        header("Expires: 0");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    
    /*
     * 美容师补贴活动列表
     * 日期 2021-07-09
     */

    public function subsidySetupList() {
        $start_time = input('param.start_time'); 
        $end_time = input('param.end_time'); 
        $store_id = input('param.store_id');
        $status = input('param.status');
        $perPage = input('param.perPage');
        $page = input('param.page');
        $agency_id = input('param.agency_id'); //代理id
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        if($start_time && $end_time){
            $where['ss.add_time'] = ['between',[$start_time,$end_time]];
        }
        if($store_id){
            $where['ss.store_id'] = $store_id;
        }  else {
            $where['ss.store_id'] = ['in',$store_ids];;
        }
        if($status){
            $where['ss.status'] = $status;
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        
        $where = isset($where)?$where:[];
        $list = Db::name('subsidy_setup')
                ->alias('ss')
                ->join('store s','s.id = ss.store_id','left')
                ->field('ss.*,s.store_name,s.type store_type')
                ->where($where)
                ->paginate($perPage);

        return Utils::ajaxReturn(0, '查询成功', $list);
    }
    
    /*
     * 美容师补贴记录
     * 日期 2021-07-09
     */

    public function subsidyList() {
        $id = input('param.id');//补贴活动id  从活动列表 点击查看补贴记录 时传这个参数
        $start_time = input('param.start_time'); 
        $end_time = input('param.end_time'); 
        $staff_name = input('param.staff_name');//美容师姓名
        $staff_phone = input('param.staff_phone');//美容师手机号
        $ord_num = input('param.ord_num');//订单号
        $store_id = input('param.store_id');//补贴门店
        $status = input('param.status');//状态 0.待入账 1.入账中 2.已入账 3.已退款  is_numeric
        $perPage = input('param.perPage');
        $page = input('param.page');
        $order_type = input('param.order_type');//订单类型  0全部 1新人专享 2秒杀 3超值组合 4i美套餐 5普通项目
        $agency_id = input('param.agency_id'); //代理id
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        $staff_type = input('param.staff_type');//美容师类型 0全部 1品牌连锁店 2服务授权店
        
        if($id){
            $where['sfn.subsidy_setup_id'] = $id;
        }  else {
            $where['sfn.subsidy_setup_id'] = ['<>',0];
        }
        if($start_time && $end_time){
            $where['sfn.add_time'] = ['between',[$start_time,$end_time]];
        }
        if($staff_name){
            $where['st.staff_name'] = ['like','%'.$staff_name.'%'];
        }
        if($staff_phone){
            $where['st.telephone'] = $staff_phone;
        }
        if($store_id){
            $where['y.store_id'] = $store_id;
        }  else {
            $where['y.store_id'] = ['in',$store_ids];
        }
        if($ord_num){
            $where['o.ord_num'] = $ord_num;
        }
        if(is_numeric($status)){
            $where['sfn.status'] = $status;
        }
        if($start_time && $end_time){
            $where['sfn.add_time'] = ['between',[$start_time,$end_time]];
        }
        if($order_type == 1){//订单类型  0全部 1新人专享 2秒杀 3超值组合 4i美套餐 5普通项目
            $where['o.first_exemption'] = 2;
        }elseif ($order_type == 2) {
            $where['o.type'] = 5;
        }elseif ($order_type == 3) {
            $where['o.type'] = 2;
        }elseif ($order_type == 4) {
            $where['o.type'] = 1;
            $where['i.is_imeigroup'] = 1;
        }elseif ($order_type == 5) {
            $where['o.type'] = 1;
            $where['i.is_imeigroup'] = 2;
            $where['o.first_exemption'] = ['<>',2];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }

        if($staff_type){
            $where['st.type'] = $staff_type;
        }
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        
        $where = isset($where)?$where:[];
        $list = Db::name('staff_flow_new')
                ->alias('sfn')
                ->join('yuyue y','y.id = sfn.order_id','left')
                ->join('order o','o.id = y.order_id','left')
                ->join('store s','s.id = y.store_id','left')
                ->join('staff st','st.id = sfn.staff_id','left')
                ->join('item i','i.id = y.item_id','left')
                ->field('sfn.id,sfn.subsidy_day_index,sfn.subsidy_money,sfn.add_time,sfn.status,o.ord_num,s.store_name,s.type store_type,st.staff_name,st.telephone,st.type staff_type,o.first_exemption,o.type,i.is_imeigroup')
                ->where($where)
                ->order('id desc')
                ->paginate($perPage)
                ->each(function($item,$key){
                    $first_exemption = $item['first_exemption'];
                    $type = $item['type'];
                    $is_imeigroup = $item['is_imeigroup'];
                    if($first_exemption == 2){
                        $item['order_type'] = 1;//订单类型  0全部 1新人专享 2秒杀 3超值组合 4i美套餐 5普通项目
                    }elseif ($type == 5) {
                        $item['order_type'] = 2;
                    }elseif ($type == 2) {
                        $item['order_type'] = 3;
                    }elseif ($type == 1 && $is_imeigroup == 1) {
                        $item['order_type'] = 4;
                    }elseif ($type == 1 && $is_imeigroup == 2 && $first_exemption != 2) {
                        $item['order_type'] = 5;
                    }
                    return $item;
                });
//                print_r(Db::name('staff_flow_new')->getLastSql());
        return Utils::ajaxReturn(0, '查询成功', $list);
    }
    
    /*
     * 美容师补贴充值记录
     * 日期 2021-07-09
     */

    public function subsidyRechargeList() {
        $start_time = input('param.start_time'); 
        $end_time = input('param.end_time'); 
        $store_id = input('param.store_id');
        $perPage = input('param.perPage');
        $page = input('param.page');
        $type = input('param.type');	//0全部1.后台充值 2.店铺转存	
        $agency_id = input('param.agency_id'); //代理id
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店
        
        if($start_time && $end_time){
            $where['sr.add_time'] = ['between',[$start_time,$end_time]];
        }
        if($store_id){
            $where['sr.store_id'] = $store_id;
        }  else {
            $where['sr.store_id'] = ['in',$store_ids];
        }
        if($type){
            $where['sr.type'] = $type;
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        
        $where = isset($where)?$where:[];
        $list = Db::name('subsidy_recharge')
                ->alias('sr')
                ->join('store s','s.id = sr.store_id','left')
                ->field('sr.*,s.store_name,s.subsidy_money,s.type store_type')
                ->where($where)
                ->paginate($perPage);
        $data['list'] = $list;
        //累计充值239.02元补贴，剩余3555.03未补贴  根据店铺返回
        if($store_id){
            $data['rechargeMoney'] = Db::name('subsidy_recharge')->where('store_id',$store_id)->sum('money');//充值金额
            $data['surplusMoney'] = Db::name('store')->where('id',$store_id)->sum('subsidy_money');//剩余补贴金额
        }  else {
            $data['rechargeMoney'] = Db::name('subsidy_recharge')->sum('money');//充值金额
            $data['surplusMoney'] = Db::name('store')->sum('subsidy_money');//剩余补贴金额
        }
        
        return Utils::ajaxReturn(0, '查询成功', $data);
    }
    
    /*
     * 项目订单列表
     * 日期 2021-07-20
     */

    public function itemOrderList() {
        $status = input('param.status');
        $vip_status = input('param.vip_status');
        $type = input('param.type');
        $start_time = input('param.start_time');
        $end_time = input('param.end_time');
        $searchStr = input('param.search_str');
        $item_name = input('param.item_name');
        $store_id = input('param.store_id');
        $agency_id = input('param.agency_id'); //代理id
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店

        $perPage = input('param.perPage');
        $page = input('param.page');

        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        $searchStr = trim($searchStr);

        if ($status) {
            $where['o.status'] = $status;
        }
        if($status == -1){
            $where['o.status'] = 0;
        }
        if ($vip_status) {
            $where['c.vip_status'] = $vip_status;
        }
        if ($type) {
            $where['o.type'] = $type;
        }  else {
            $where['o.type'] = ['<>',7];
        }
        if ($end_time && $start_time) {
            $where['o.pay_time'] = array('between', array($start_time, $end_time));
        } elseif (empty($end_time) && $start_time) {
            $time = strtotime($start_time);
            $time2 = $time + 24 * 60 * 60;
            $time2 = date('Y-m-d H:i:s', $time2);
            $where['o.pay_time'] = array('between', array($time, $time2));
        }
        if($item_name && $type==1){
            $where['i.item_name'] = ['like','%'.$item_name.'%'];
        }
        
        if($store_id){
            $where['s.id'] = $store_id;
        }  else {
            $where['s.id'] = ['in',$store_ids];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }
        $where = isset($where) ? $where : [];
        if ($searchStr) {
            $orderList = Order::alias('o')
                    ->join('custom c', 'c.id = o.ctm_id', 'left')
                    ->join('store s','s.id = o.store_id','left')
                    ->join('ord_pro op','op.ord_id = o.id','left')
                    ->join('item i','i.id = op.pro_id','left')
                    ->where($where)
                    ->where(function ($query) use ($searchStr) {
                        $query->where('c.custom_name', 'like', '%' . $searchStr . '%')
                        ->whereOr('c.custom_phone', 'like', '%' . $searchStr . '%')
                        ->whereOr('o.ord_num', 'like', '%' . $searchStr . '%');
                    })
                    ->field('o.id,o.ord_num,o.status,o.pay_time,o.type,o.pay_money,o.balance_money,o.red_money,o.special_id,c.custom_name,c.custom_phone,c.vip_status,s.store_name,s.type store_type,op.pro_id')
                    ->order('o.id desc')
                    ->paginate($perPage);
        } else {
            $orderList = Order::alias('o')
                    ->join('custom c', 'c.id = o.ctm_id', 'left')
                    ->join('store s','s.id = o.store_id','left')
                    ->join('ord_pro op','op.ord_id = o.id','left')
                    ->join('item i','i.id = op.pro_id','left')
                    ->where($where)
                    ->field('o.id,o.ord_num,o.status,o.pay_time,o.type,o.pay_money,o.balance_money,o.red_money,o.special_id,c.custom_name,c.custom_phone,c.vip_status,s.store_name,s.type store_type,op.pro_id')
                    ->order('o.id desc')
                    ->paginate($perPage);
        }
        //查询订单购买的项目名称
        foreach ($orderList as $k => $v){
            $type = $v['type'];
            $pro_id = $v['pro_id'];
            //类型 1项目 2超值组合 3充值 4 特权卡5秒杀6体验特权卡7医美项目	
            if($type == 1){
                $item_name = Db::name('item')->where('id',$pro_id)->value('item_name');
            }elseif ($type == 2) {
                $item_name = Db::name('overgroup')->where('id',$pro_id)->value('name');
            }elseif ($type == 3) {
                $item_name = '充值';
            }elseif ($type == 4) {
                $item_name = '特权卡';
            }elseif ($type == 5) {
                $miaoshaRow = Db::name('miaosha')->where('id',$pro_id)->field('name,item_id')->find();
                $item_name = Db::name('item')->where('id',$miaoshaRow['item_id'])->value('item_name');
                $item_name = $miaoshaRow['name'].'-'.$item_name;
            }elseif ($type == 6) {
                $item_name = '体验特权卡';
            }elseif ($type == 7) {
                $item_name = Db::name('order_yimei')->alias('y')
                        ->join('item i','i.id = y.item_id','left')
                        ->where('y.ord_id',$v['id'])
                        ->value('i.item_name');
            }
            $orderList[$k]['item_name'] = $item_name;
        }
        return $this->ajaxReturn(0, '查询成功', $orderList);
    }
    
    /*
     * 项目退款列表
     * author xfwed 
     * date 2021.07.20
     */

    public function itemRefundList() {
        $refund_status = input('param.refund_status'); //退款状态
        $ord_num = input('param.ord_num');
        $perPage = input('param.perPage');
        $page = input('param.page');
        $status = input('param.status'); //预约状态
        $start_time = input('param.start_time');
        $end_time = input('param.end_time');
        $phone = input('param.phone');
        $refund_type = input('param.refund_type');//退款类型 1.自动 2.审核
        $store_id = input('param.store_id');
        $agency_id = input('param.agency_id'); //代理id
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        $staff_type = input('param.staff_type');//美容师类型 0全部 1品牌连锁店 2服务授权店

        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
//        if ($refund_status) {
//            $where['y.refund_status'] = $refund_status;
//        } else {
//            $where['y.refund_status'] = ['in', '1,2,3'];
//        }
        if($refund_type){
            $where['y.refund_type'] = $refund_type;
        }  
        if ($refund_status) {
            $where['p.status'] = $refund_status;
        } else {
            $where['y.refund_status'] = ['in', '1,2,3'];
        }
        if ($ord_num) {
            $where['o.ord_num'] = $ord_num;
        }
        if ($status) {
            $where['y.status'] = $status;
        }
        if ($phone) {
            $where['c.custom_phone'] = $phone;
        }
        
        if ($end_time && $start_time) {
            $where['p.add_time'] = array('between', array($start_time, $end_time));
        } elseif (empty($end_time) && $start_time) {
            $time = strtotime($start_time);
            $time2 = $time + 24 * 60 * 60;
            $time2 = date('Y-m-d H:i:s', $time2);
            $where['p.add_time'] = array('between', array($time, $time2));
        }
        if($store_id){
            $where['s.id'] = $store_id;
        }  else {
            $where['s.id'] = ['in',$store_ids];
        }
        if($store_type){
            $where['s.type'] = $store_type;
        }

        if($staff_type){
            $where['st.type'] = $staff_type;
        }
        //$where['p.status'] = 1;
        $subsql = Db::table('ai_program_refund')
                //->where('id','in',"SELECT MAX('id') FROM ai_program_refund GROUP BY 'yuyue_id'")
                ->where("id in (SELECT MAX(`id`) FROM `ai_program_refund` GROUP BY `yuyue_id`)")
                ->field('id pid,yuyue_id,status,add_time')
                ->buildSql();
        $list = Db::table('ai_yuyue')->alias('y')
                ->join('custom c', 'c.id = y.ctm_id', 'left')
                ->join('item i', 'i.id = y.item_id', 'left')
                ->join('order o', 'o.id = y.order_id', 'left')
                ->join('store s', 's.id = y.store_id', 'left')
                ->join('staff st', 'st.id = y.staff_id', 'left')
                ->join([$subsql => 'p'],'p.yuyue_id=y.id','left')
                ->where($where)
                ->group('y.id')
                ->order('p.add_time asc,p.pid asc')
                ->field('y.id,y.status,y.money,y.refund_status,y.refund_type,c.custom_name,c.custom_phone,c.vip_status,i.item_name,o.ord_num,s.store_name,s.type store_type,st.staff_name,st.type staff_type,p.add_time,p.status pstatus')
                ->paginate($perPage);
//        print_r(Yuyue::getLastSql());
        return $this->ajaxReturn(0, '查询成功', $list);
    }
    
    /*
     * 预约列表
     * author xfwed 
     * date 2021.07.20
     */

    public function itemyuyueList() {
        $admin = new Item();
        $staff_name = input('param.staff_name');
        $ctm_name = input('param.ctm_name');
        $ctm_phone = input('param.ctm_phone');
        //$store_name = input('param.store_name');
        $order_num = input('param.order_num');
        $perPage = input('param.perPage');
        $page = input('param.page');
        $status = input('param.status');
        $store_id = input('param.store_id');
        $agency_id = input('param.agency_id'); //代理id
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);
        $store_type = input('param.store_type');//店铺类型 0全部 1品牌连锁店 2服务授权店	
        $staff_type = input('param.staff_type');//美容师类型 0全部 1品牌连锁店 2服务授权店

        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        if ($staff_name) {
            $where['s.staff_name'] = ['like', '%' . $staff_name . '%'];
        }
        if ($ctm_name) {
            $where['c.custom_name'] = ['like', '%' . $ctm_name . '%'];
        }
        if ($ctm_phone) {
            $where['c.custom_phone'] = $ctm_phone;
        }
        if ($order_num) {
            $where['o.ord_num'] = $order_num;
        }

        if ($status) {
            $where['y.status'] = $status;
        }
        if($store_id){
            $where['st.id'] = $store_id;
        }  else {
            $where['st.id'] = ['in',$store_ids];
        }
        if($store_type){
            $where['st.type'] = $store_type;
        }
        if($staff_type){
            $where['s.type'] = $staff_type;
        }

//        $where['i.is_del'] = 1;
        $where = isset($where) ? $where : [];
        
        $yuyuList = Yuyue::alias('y')
                ->join('staff s','s.id = y.staff_id','left')
                ->join('custom c','c.id = y.ctm_id','left')
                ->join('store st','st.id = y.store_id','left')
                ->join('order o','o.id = y.order_id','left')
                ->join('item i','i.id = y.item_id','left')
                ->field('y.*,s.staff_name,s.type staff_type,c.custom_name,st.store_name,st.type store_type,o.ord_num,i.item_name')
                ->where($where)
                ->order('y.id desc')
                ->paginate($perPage);
        
        return $this->ajaxReturn(0, '查询成功', $yuyuList);
    }
    
    /*
     * 用户列表
     * author xfwed 
     * date 2019.09.16
     */

    public function customList() {
        $vipStatus = input('param.vip_status');
        $searchStr = input('param.search_str');
        $is_smallshop = input('param.is_smallshop');
        $smallshop_type = input('param.smallshop_type');
        $perPage = input('param.perPage');
        $page = input('param.page');
        $warning_level = input('param.warning_level');//预警等级-1全部 0正常  1死亡预警  2黑色预警 3红色预警 4黄色预警
        $start_time = input('param.start_time');
        $end_time = input('param.end_time');
        $exclusive_store_id = input('param.exclusive_store_id');//专属店铺	
        $exclusive_staff_id = input('param.exclusive_staff_id');//专属美容师
        
        $store_id = input('param.store_id');
        $agency_id = input('param.agency_id'); //代理id
        $store_ids = Store::where('agency_id',$agency_id)->column('id');
        $store_ids = implode(',', $store_ids);

        $vipStatus = isset($vipStatus) ? $vipStatus : 0;
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        $searchStr = trim($searchStr);
        if ($vipStatus) {
            $where['vip_status'] = $vipStatus;
        }
        if ($is_smallshop) {
            $where['is_smallshop'] = $is_smallshop;
        }
        if (isset($smallshop_type)) {
            $where['smallshop_type'] = $smallshop_type;
        }
        if(preg_match("/^[0-9][0-9]*$/" ,$warning_level)){
            $where['warning_level'] = $warning_level;
        }
        if($start_time && $end_time){
            $where['add_time'] = ['between',[$start_time,$end_time]];
        }
        if($exclusive_store_id){
            $where['exclusive_store_id'] = $exclusive_store_id;
        }
        if($exclusive_staff_id){
            $where['exclusive_staff_id'] = $exclusive_staff_id;
        }
        if($store_id){
            $s = $store_id;
        }  else {
            $s = $store_ids;
        }
        $ctmIds = Db::name('store_ctm')
                ->where('store_id','in',$s)
                ->group('ctm_id')
                ->column('ctm_id');
        $where['id'] = ['in',$ctmIds];
        //排序 1.注册时间新->旧2.注册时间旧->新3.开通特权时间新->旧4.开通特权时间旧->新5.消费高->低6.消费低->高
        //7.服务次数多->少8.服务次数少->多9.美容金高->低10.美容金低->高11.最近消费新->旧12.最近消费旧->新
        //13.进店次数多->少14.进店次数少->多15.预警等级高->低16.预警等级低->高
        $orderType = input('param.orderType');
        $order = 'id desc';
        switch ($orderType) {
            case 1 :
                $order = 'add_time desc, id desc';
                break;
            case 2 :
                $order = 'add_time asc, id desc';
                break;
            case 3 :
                $order = 'special_time desc, id desc';
                break;
            case 4 :
                $order = 'special_time asc,id desc';
                break;
            case 5 :
                $order = 'consume_money desc, id desc';
                break;
            case 6 :
                $order = 'consume_money asc, id desc';
                break;
            case 7 :
                $order = 'ser_num desc, id desc';
                break;
            case 8 :
                $order = 'ser_num asc, id desc';
                break;
            case 9 :
                $order = 'red_money desc, id desc';
                break;
            case 10 :
                $order = 'red_money asc, id desc';
                break;
            case 11 :
                $order = 'sell_time desc, id desc';
                break;
            case 12 :
                $order = 'sell_time asc, id desc';
                break;
            case 13 :
                $order = 'visit_num desc, id desc';
                break;
            case 14 :
                $order = 'visit_num asc, id desc';
                break;
            case 15 :
                $order = 'warning_level=0 asc,warning_level asc, id desc';
                break;
            case 16 :
                $order = 'warning_level=0 desc,warning_level desc, id desc';
                break;
            default :
                $order = 'id desc';
        }
        
        $where = isset($where) ? $where : [];
        if ($searchStr) {
            $customList = Custom::where($where)
                    ->where(function ($query) use ($searchStr) {
                        $query->where('custom_name', 'like', '%' . $searchStr . '%')
                        ->whereOr('custom_phone', 'like', '%' . $searchStr . '%');
                    })
                    ->field('id,custom_name,custom_phone,vip_status,consume_money,real_money,tx_money,add_time,longitude,latitude,warning_level,exclusive_store_id,exclusive_staff_id,ser_num,sell_time,visit_num,red_money')
                    ->orderRaw($order)
                    ->paginate($perPage);
        } else {
            $customList = Custom::where($where)
                    ->field('id,custom_name,custom_phone,vip_status,consume_money,real_money,tx_money,add_time,longitude,latitude,warning_level,exclusive_store_id,exclusive_staff_id,ser_num,sell_time,visit_num,red_money')
                    ->orderRaw($order)
                    ->paginate($perPage);
        }
        foreach ($customList as $k => $v) {
            //用户消费金额
            $payMoney = Order::where('ctm_id', '=', $v['id'])
                    ->where('status', '=', 2)
                    ->sum('pay_money');
            $refund = Yuyue::where('ctm_id',$v['id'])->where('status',6)->sum('money');
            //$customList[$k]['consume_money'] = $payMoney - $refund;
            $customList[$k]['refund_money'] = $refund;
            //查询用户的订单数
            $customList[$k]['orderNum'] = Order::where('ctm_id', '=', $v['id'])
                    ->where('status', '=', 2)
                    ->count();
            //用户消费次数
            $customList[$k]['serNum'] = Yuyue::where('ctm_id', '=', $v['id'])
                    ->where('status', 'in', '4,7,8')
                    ->count();
            //用户消费门店
            $customList[$k]['serStoreNum'] = Yuyue::where('ctm_id', '=', $v['id'])
                    ->where('status', 'in', '4,7,8')
                    ->group('store_id')
                    ->count();
            //关注的美容师
            $customList[$k]['collectStaff'] = CtmCollect::where('ctm_id', '=', $v['id'])
                    ->where('type', '=', 2)
                    ->count();
            //收藏的项目
            $customList[$k]['collectItem'] = CtmCollect::where('ctm_id', '=', $v['id'])
                    ->where('type', '=', 1)
                    ->count();
            //查询用户的充值金额
            $customList[$k]['payMoney'] = Order::where('ctm_id', '=', $v['id'])
                    ->where('status', '=', 2)
                    ->where('type', '=', 3)
                    ->sum('pay_money');
            //查询用户开通特权的时间
            if ($v['vip_status'] == 2 || $v['vip_status'] == 3) {
                $customList[$k]['special_time'] = CtmSpecial::where('ctm_id', $v['id'])->value('add_time');
            }
            //查询用户的专属店铺
            $exclusive_store_id = $v['exclusive_store_id'];
            $customList[$k]['exclusive_store_name'] = Store::where('id',$exclusive_store_id)->value('store_name');
            //查询用户的专属美容师
            $exclusive_staff_id = $v['exclusive_staff_id'];
            $customList[$k]['exclusive_staff_name'] = Staff::where('id',$exclusive_staff_id)->value('staff_name');
            $custom_phone = $v['custom_phone'];
            $custom_phone = mb_substr($custom_phone, 0,3).'****'.mb_substr($custom_phone, 7);
            $customList[$k]['custom_phone'] = $custom_phone;
           }

        return $this->ajaxReturn(0, '查询成功', $customList);
    }
    
    /*
     * 用户详情
     * author xfwed 
     * date 2021.08.18
     */

    public function customInfo() {
        $id = input('param.id');

        $customList = Custom::where('id', '=', $id)
                ->field('id,custom_photo,custom_name,custom_phone,vip_status,consume_money,real_money,tx_money,red_money,add_time,birthday,is_smallshop,smallshop_storeid,idcard1,idcard2,cardnum')
                ->find();
        $customList['custom_sex'] = 2;
        $customList['height'] = '未知';
        $customList['weight'] = '未知';
        $customList['health'] = rand(60, 99);
        $customList['skin'] = [];
        $customList['fat'] = [];
        //查询用户的订单数
        $customList['orderNum'] = Order::where('ctm_id', '=', $customList['id'])
                ->where('status', '=', 2)
                ->count();

        //关注的美容师
        $customList['collectStaff'] = CtmCollect::where('ctm_id', '=', $customList['id'])
                ->where('type', '=', 2)
                ->count();
        //收藏的项目
        $customList['collectItem'] = CtmCollect::where('ctm_id', '=', $customList['id'])
                ->where('type', '=', 1)
                ->count();
        //喜欢的店铺
        $customList['collectStore'] = CtmCollect::where('ctm_id', '=', $customList['id'])
                ->where('type', '=', 3)
                ->count();
        //查询用户的充值金额
        $customList['payMoney'] = Order::where('ctm_id', '=', $customList['id'])
                ->where('status', '=', 2)
                ->where('type', '=', 3)
                ->sum('pay_money');
        //用户代金券数
        $customList['cunNum'] = CtmCun::where('ctm_id', $customList['id'])->where('employ', 2)->count();
        //用户服务技师
        $customList['staff'] = Yuyue::alias('y')
                ->join('staff s', 's.id = y.staff_id', 'left')
                ->where('y.ctm_id', $customList['id'])
                ->where('y.status', '4,7,8')
                ->value('s.staff_name');
        //用户小店分瑞的店铺
        $customList['storeName'] = Store::where('id',$customList['smallshop_storeid'])->value('store_name');
        //用户小店收益
        $customList['smallStoreIncome'] = CtmFlow::where('ctm_id',$id)->where('pro_type','in','8,9')->sum('money');

        return $this->ajaxReturn(0, '查询成功', $customList);
    }
    /*
     * 用户消费订单
     * author xfwed 
     * date 2021.08.18
     */

    public function ctmOrder() {
        $ctm_id = input('param.ctm_id'); //用户id
        $type = input('param.type'); //1项目2商品
        $status = input('param.status'); //订单状态
        $mini_shop_id = input('param.mini_shop_id');//1是2否小店订单
        $start_time = input('param.start_time');
        $end_time = input('param.end_time');

        $perPage = input('param.perPage');
        $page = input('param.page');
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;

        if ($type == 1) {
            if ($end_time && $start_time) {
                $where['o.pay_time'] = array('between', array($start_time, $end_time));
            } elseif (empty($end_time) && $start_time) {
                $time = strtotime($start_time);
                $time2 = $time + 24 * 60 * 60;
                $time2 = date('Y-m-d H:i:s', $time2);
                $where['o.pay_time'] = array('between', array($time, $time2));
            }


            $where = isset($where) ? $where : [];
            if ($status) {
                $where['o.status'] = $status;
            }
            if ($status == -1) {
                $where['o.status'] = 0;
            }
            if($mini_shop_id == 1){
                $where['o.mini_shop_id'] = ['<>',0];
            }elseif ($mini_shop_id == 2) {
                $where['o.mini_shop_id'] = 0;
            }
            $where['type'] = ['<>',7];
            $orderList = Order::alias('o')
                    ->join('custom c', 'c.id = o.ctm_id', 'left')
                    ->where($where)
                    ->where('o.ctm_id', $ctm_id)
                    ->field('o.id,o.ord_num,o.status,o.pay_time,o.type,o.pay_money,o.balance_money,o.red_money,o.special_id,c.custom_name,c.custom_phone,c.vip_status')
                    ->order('o.id desc')
                    ->paginate($perPage);
        } elseif ($type == 2) {
            if ($status) {
                $where['pd.status'] = $status;
            }
            if ($status == -1) {
                $where['pd.status'] = 0;
            }
            
            if($mini_shop_id == 1){
                $where['pd.mini_shop_id'] = ['<>',0];
            }elseif ($mini_shop_id == 2) {
                $where['pd.mini_shop_id'] = 0;
            }

            if ($end_time && $start_time) {
                $where['pd.add_time'] = array('between', array($start_time, $end_time));
            } elseif (empty($end_time) && $start_time) {
                $time = strtotime($start_time);
                $time2 = $time + 24 * 60 * 60;
                $time2 = date('Y-m-d H:i:s', $time2);
                $where['pd.add_time'] = array('between', array($time, $time2));
            }
            //$where['m.machine_type'] = 1;
            $where['pd.ctm_id'] = $ctm_id;
            $orderList = Db::table('pay_order_detail')->alias('pd')
                    ->join(['pay_order_goods' => 'pg'], 'pg.pay_order_id = pd.id', 'left')
                    ->join('store s', 's.id = pd.store_id', 'left')
                    ->join('custom c', 'c.id=pd.ctm_id', 'left')
                    ->join(['brand_info' => 'b'], 'b.id = pd.brand_id', 'left')
                    //->join(['da_machine_store_relation' => 'm'], 'm.store_id = pd.store_id')
                    ->where($where)
                    ->field('pd.id,pd.order_id,pg.name,pg.goods_icon,s.store_name,pd.add_time create_time,c.custom_name,c.custom_phone,pd.pay_money,pd.status,b.name brand_name')
//                ->group('o.id')
                    ->paginate($perPage);
        }
        return $this->ajaxReturn(0, '查询成功', $orderList);
    }
    
    /*
     * 用户零钱/代金券
     * author xfwed 
     * date 2021.08.18
     */

    public function ctmCollectCunList() {
        $id = input('param.id');

        $perPage = input('param.perPage');
        $page = input('param.page');
        $perPage = isset($perPage) ? $perPage : 10;
        $page = isset($page) ? $page : 1;
        //代金券明细
        $cunList = CtmCun::alias('cc')
                ->join('coupon c', 'c.id=cc.cun_id', 'left')
                ->where('cc.ctm_id', $id)
                ->field('c.id,c.name,c.money,cc.cun_id,cc.add_time,cc.end_time,cc.employ')
                ->paginate($perPage);
        foreach ($cunList as $k => $v) {
            if ($v['employ'] == 1) {
                $orderRow = Order::where('cun_id', $v['cun_id'])->field('ord_num,pay_time')->find();
                $cunList[$k]['ord_num'] = $orderRow['ord_num'];
                $cunList[$k]['pay_time'] = $orderRow['pay_time'];
            } else {
                $cunList[$k]['ord_num'] = NULL;
                $cunList[$k]['pay_time'] = NULL;
            }
        }
        $data['cunList'] = $cunList;
        //流水记录
        $flowList = CtmFlow::where('ctm_id', $id)
                ->field('id,item_name,type,pro_type,money,add_time')
                ->order('id desc')
                //->paginate($perPage);
                ->paginate($perPage,'',['var_page'=>'page1'])
                ->appends(['ctm_id'=>$id]);
        $data['flowList'] = $flowList;
        return $this->ajaxReturn(0, '查询成功', $data);
    }
    
    
}
